package generator.main.utils;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import generator.main.bean.Table;
import generator.main.bean.TableField;

public class DBconcetion {
	/**
	 * 读取properties 配置文件
	 * @param 	propertiesPath   文件路径
	 * @return	properties
	 * @throws 	Exception
	 */
	public static Properties getProperties (String propertiesPath) throws Exception{
		Properties pro = new Properties();
		FileInputStream in = new FileInputStream(propertiesPath);
		pro.load(in);
		in.close();
		return pro;
	}
	/**
	 * 获取数据库连接
	 * @param dbPropertiesPath  数据库配置文件
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection(String dbPropertiesPath) throws Exception {
		/** 读取配置文件里的数据库连接名称，url,密码*/
		Properties pro = getProperties(dbPropertiesPath);
		String url = pro.getProperty("url");
		String password = pro.getProperty("password");
		String userName = pro.getProperty("user");
		String driverclass = pro.getProperty("driverclass");
		Class.forName(driverclass);
		Connection con= DriverManager.getConnection(url, userName, password);
		if(con == null) {
			throw new RuntimeException("数据库连接失败！");
		}else {
			return con;
		}
	}
	
	
	
	
    /**
     * 根据传入的表名，库名，映射表字段名称，类型，备注
     * @param con				connection连接
     * @param table				表名，库名
     * @return	Map<String, 	List<TableField>> :
     * 1：tableAllFiledList  	表中所有的字段
     * 2：tableFiledList  		不包含sts，创建人，创建人时间，修改人，修改人时间的集合
     * 3：insertTableFiledList  	插入字段的集合，不包含id，创建时间，修改时间，sts
     * 4：updateTableFiledList  	修改字段的集合，不包含：修改时间，sts
     * @throws SQLException
     */
	public static Map<String, List<TableField>> getTableField(Connection con, Table table,String dbType) throws SQLException {
		/**
		 * 根据表名称查询数据库字段，不查询id,sts,create_user_id,create_time,modify_user_id,modify_time
		 */
		String sql =null;
		if("mysql".equals(dbType)) {
			sql = "select COLUMN_NAME as name ,column_comment as comment ,DATA_TYPE as type from INFORMATION_SCHEMA.Columns" + " where table_name=" + "'" + table.getTableName() + "'" + " and table_schema=" + "'" +table.getSchema() +"'";
		}
		if("sqlserver".equals(dbType)) {
			sql = "SELECT  CAST (d.name AS VARCHAR(100))   AS table_name, 	CAST (a.name  AS VARCHAR(100))   AS name,  CAST (b.name AS VARCHAR(100))  AS type,   CAST (g.VALUE AS VARCHAR(100))  as comment FROM  syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'left join syscomments e on  a.cdefault=e.id left join sys.extended_properties   g on  a.id=G.major_id and a.colid=g.minor_id left join  sys.extended_properties f  on d.id=f.major_id and f.minor_id=0  where  d.name=" + "'" + table.getTableName() + "'" +"" ;
		}
		/** 获取sql执行器*/
		Statement statement = con.createStatement();
		/** 获取结果集*/
		ResultSet resutSet = statement.executeQuery(sql);
		/** 所有字段的集合*/
		List<TableField> tableAllFiledList = new ArrayList<TableField>();
		/** 不包含sts，创建人，创建人时间，修改人，修改人时间的集合*/
		List<TableField> tableFiledList = new ArrayList<TableField>();
		/** 插入字段的集合，不包含id，创建时间，修改时间，sts*/
		List<TableField> insertTableFiledList = new ArrayList<TableField>();
		/** 修改字段的集合，修改时间，sts*/
		List<TableField> updateTableFiledList = new ArrayList<TableField>();
		while (resutSet.next()) {
			String type = resutSet.getString("type");
			String name = resutSet.getString("name");
			String comment = resutSet.getString("comment");
			TableField filed = new TableField();
			filed.setType(type);
			filed.setName(name);
			filed.setComment(comment);
			/** 所有字段的集合*/
			tableAllFiledList.add(filed);
			if(!("id".equals(name)) && !"create_user_id".equals(name) && !"create_time".equals(name) && !"modify_user_id".equals(name) && !"modify_time".equals(name) && !"sts".equals(name) ) {
				tableFiledList.add(filed);
			}
			/** 插入字段*/
			if(!"id".equals(name) && !"create_time".equals(name) && !"modify_time".equals(name) && !"sts".equals(name) ) {
				insertTableFiledList.add(filed);
			}
			/** 修改字段*/
			if(!"modify_time".equals(name)) {
				updateTableFiledList.add(filed);
			}
		}
		HashMap<String, List<TableField>> map=new HashMap<String,  List<TableField>>();
		map.put("tableFiledList", tableFiledList);
		map.put("tableAllFiledList", tableAllFiledList);
		map.put("insertTableFiledList", insertTableFiledList);
		map.put("updateTableFiledList", updateTableFiledList);
		return map;
	}

	
	
	 
}
